"""
Excel生产订单号拆分工具
Copyright (c) 2025 hmlovelm@163.com. 保留所有权利.

本项目采用 LGPL-3.0 许可证。
允许在遵守许可证条款的前提下自由使用、修改和分发。

版本: 1.0.0
作者: YH_station
邮箱: hmlovelm@163.com
项目地址: https://gitee.com/yh-station/python-split-excel
"""
import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import openpyxl
from openpyxl.utils import get_column_letter
import os
import threading
import time

class ExcelProcessor:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel数据处理工具 - 生产订单号拆分")
        self.root.geometry("800x600")
        
        # 创建UI元素
        self.create_widgets()
        
        # 初始化状态
        self.update_status("🚀 Excel生产订单号拆分工具\n")
        self.update_status("="*50 + "\n")
        self.update_status("💡 功能说明:\n")
        self.update_status("• 自动拆分'ERP生产订单号'列中的逗号分隔值\n")
        self.update_status("• 每行生成新的凭证业务编码和订单号组合\n") 
        self.update_status("• 智能处理合并单元格和格式问题\n")
        self.update_status("• 支持中英文逗号自动识别\n")
        self.update_status("="*50 + "\n\n")
        self.update_status("📝 请选择Excel文件开始处理...\n\n")
        
        # 禁用处理按钮和保存按钮
        self.process_button.config(state=tk.DISABLED)
        self.save_button.config(state=tk.DISABLED)
        
        # 文件路径
        self.file_path = None
        self.workbook = None
        self.sheet = None
        
        # 进度相关变量
        self.progress_value = 0
        self.progress_max = 100
        
    def create_widgets(self):
        # 创建标题
        title_label = tk.Label(self.root, text="Excel生产订单号拆分工具", 
                              font=("Arial", 16, "bold"), fg="#2c3e50")
        title_label.pack(pady=10)
        
        # 创建按钮框架
        button_frame = tk.Frame(self.root)
        button_frame.pack(pady=10)
        
        # 选择文件按钮
        self.select_button = tk.Button(button_frame, text="📁 选择Excel文件", 
                                      command=self.select_file,
                                      bg="#3498db", fg="white",
                                      font=("Arial", 10, "bold"),
                                      padx=15, pady=8)
        self.select_button.pack(side=tk.LEFT, padx=5)
        
        # 处理数据按钮
        self.process_button = tk.Button(button_frame, text="⚡ 处理数据", 
                                       command=self.process_data,
                                       bg="#2ecc71", fg="white", 
                                       font=("Arial", 10, "bold"),
                                       padx=15, pady=8)
        self.process_button.pack(side=tk.LEFT, padx=5)
        
        # 保存结果按钮
        self.save_button = tk.Button(button_frame, text="💾 保存结果", 
                                    command=self.save_file,
                                    bg="#e74c3c", fg="white",
                                    font=("Arial", 10, "bold"),
                                    padx=15, pady=8)
        self.save_button.pack(side=tk.LEFT, padx=5)
        
        # 进度条框架
        progress_frame = tk.Frame(self.root)
        progress_frame.pack(fill=tk.X, padx=10, pady=5)
        
        # 进度标签
        self.progress_label = tk.Label(progress_frame, text="准备就绪", 
                                      font=("Arial", 9))
        self.progress_label.pack(anchor=tk.W)
        
        # 进度条 - 修复：使用determinate模式
        self.progress = ttk.Progressbar(progress_frame, 
                                       orient=tk.HORIZONTAL, 
                                       length=100, 
                                       mode='determinate')
        self.progress.pack(fill=tk.X, pady=5)
        
        # 百分比标签
        self.percent_label = tk.Label(progress_frame, text="0%", 
                                     font=("Arial", 9, "bold"))
        self.percent_label.pack(anchor=tk.E)
        
        # 创建状态文本框框架
        text_frame = tk.Frame(self.root)
        text_frame.pack(padx=10, pady=5, fill=tk.BOTH, expand=True)
        
        # 状态标签
        status_label = tk.Label(text_frame, text="处理日志:", 
                               font=("Arial", 10, "bold"))
        status_label.pack(anchor=tk.W)
        
        # 创建状态文本框
        self.status_text = tk.Text(text_frame, wrap=tk.WORD, 
                                  font=("Consolas", 9),
                                  bg="#f8f9fa", fg="#2c3e50")
        self.status_text.pack(fill=tk.BOTH, expand=True)
        
        # 添加滚动条
        scrollbar = tk.Scrollbar(self.status_text)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
        self.status_text.config(yscrollcommand=scrollbar.set)
        scrollbar.config(command=self.status_text.yview)
        
    def update_status(self, message):
        """更新状态文本框"""
        self.status_text.insert(tk.END, message)
        self.status_text.see(tk.END)
        self.root.update_idletasks()
    
    def update_progress(self, value, max_value=None, text=None):
        """更新进度条"""
        if max_value is not None:
            self.progress_max = max_value
            self.progress.config(maximum=max_value)
        
        self.progress_value = value
        self.progress['value'] = value
        
        # 计算百分比
        if self.progress_max > 0:
            percent = int((value / self.progress_max) * 100)
            self.percent_label.config(text=f"{percent}%")
        
        if text:
            self.progress_label.config(text=text)
        
        self.root.update_idletasks()
    
    def select_file(self):
        """选择Excel文件"""
        file_path = filedialog.askopenfilename(
            title="选择Excel文件",
            filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
        )
        
        if file_path:
            self.file_path = file_path
            filename = os.path.basename(file_path)
            self.update_status(f"✅ 已选择文件: {filename}\n")
            self.update_status("📂 正在打开文件...\n")
            
            try:
                # 重置进度条
                self.update_progress(0, 100, "正在打开文件...")
                
                # 打开工作簿
                self.workbook = openpyxl.load_workbook(file_path)
                self.sheet = self.workbook.active
                
                self.update_progress(100, 100, "文件打开成功")
                self.update_status(f"✅ 文件已成功打开\n")
                self.update_status(f"📊 工作表: {self.sheet.title}\n")
                self.update_status(f"📈 数据范围: A1:{get_column_letter(self.sheet.max_col)}{self.sheet.max_row}\n")
                
                # 检查是否有合并单元格
                if self.sheet.merged_cells.ranges:
                    self.update_status("⚠️  检测到合并单元格，将自动取消合并...\n")
                
                # 启用处理按钮
                self.process_button.config(state=tk.NORMAL)
                
            except Exception as e:
                self.update_status(f"❌ 错误: {str(e)}\n")
                messagebox.showerror("错误", f"无法打开文件: {str(e)}")
    
    def process_data(self):
        """处理数据"""
        if not self.workbook or not self.sheet:
            self.update_status("❌ 错误: 请先选择Excel文件\n")
            return
            
        # 在新线程中处理数据，避免UI卡顿
        thread = threading.Thread(target=self._process_data_thread)
        thread.daemon = True
        thread.start()
    
    def _process_data_thread(self):
        """处理数据的线程函数"""
        try:
            self.update_progress(0, 100, "开始处理数据...")
            self.update_status("\n🔧 开始处理数据...\n")
            
            # 处理合并单元格
            self._handle_merged_cells()
            
            # 查找标题行位置
            header_row = list(self.sheet.iter_rows(min_row=1, max_row=1, values_only=True))[0]
            
            # 初始化列号变量
            col_voucher = None
            col_order = None
            
            # 查找列位置
            for idx, cell_value in enumerate(header_row, 1):
                if cell_value == "客户订单":
                    col_voucher = idx
                elif cell_value == "ERP生产订单号":
                    col_order = idx
            
            # 检查是否找到需要的列
            if col_voucher is None or col_order is None:
                self.update_status("❌ 错误: 未找到【客户订单】或【ERP生产订单号】列\n")
                return
                
            self.update_status(f"✅ 找到目标列:\n")
            self.update_status(f"   • 客户订单({get_column_letter(col_voucher)}列)\n")
            self.update_status(f"   • ERP生产订单号({get_column_letter(col_order)}列)\n")
            
            # 计算总数据行数
            last_data_row = self.sheet.max_row
            total_original_rows = last_data_row - 1
            self.update_status(f"📊 数据总行数: {total_original_rows}\n")
            
            # 预计算拆分后的总行数
            self.update_progress(10, 100, "正在分析数据...")
            total_rows = 0
            for i in range(2, last_data_row + 1):
                order_value = self._get_cell_value(i, col_order)
                if order_value:
                    total_rows += len(str(order_value).split(","))
            
            self.update_status(f"📈 预估拆分后行数: {total_rows}\n")
            
            # 准备结果数组
            output_data = []
            
            # 处理每一行数据
            result_count = 0
            current_row = 0
            
            self.update_progress(20, 100, "正在拆分订单号...")
            
            for i in range(2, last_data_row + 1):
                current_row = i
                # 更新进度
                progress = 20 + int((i - 2) / (last_data_row - 1) * 60)
                self.update_progress(progress, 100, f"处理中: {i-1}/{total_original_rows} 行")
                
                # 获取凭证列值
                voucher_value = self._get_cell_value(i, col_voucher)
                if voucher_value is None:
                    voucher_value = ""
                
                # 处理单号列
                order_value = self._get_cell_value(i, col_order)
                if order_value is None:
                    order_value = ""
                
                # 统一分隔符为英文逗号
                order_text = str(order_value).replace("，", ",")
                
                # 拆分单号字符串
                order_list = [order.strip() for order in order_text.split(",") if order.strip()]
                
                # 遍历每个拆分出的单号
                for order in order_list:
                    output_data.append((voucher_value, order))
                    result_count += 1
                
                # 每处理50行更新一次状态
                if (i - 1) % 50 == 0:
                    self.update_status(f"✅ 已处理 {i-1}/{total_original_rows} 行，已生成 {result_count} 行结果\n")
            
            self.update_progress(85, 100, "正在写入结果...")
            
            # 清空原始数据区域
            for i in range(2, last_data_row + 1):
                self.sheet.cell(row=i, column=col_voucher).value = None
                self.sheet.cell(row=i, column=col_order).value = None
            
            # 设置文本格式防止科学计数法
            for col in [col_voucher, col_order]:
                for i in range(2, 2 + len(output_data)):
                    cell = self.sheet.cell(row=i, column=col)
                    cell.number_format = '@'
            
            # 写入结果数据
            for i, (voucher, order) in enumerate(output_data, 2):
                self.sheet.cell(row=i, column=col_voucher).value = voucher
                self.sheet.cell(row=i, column=col_order).value = order
            
            # 设置标题行格式
            for col in [col_voucher, col_order]:
                cell = self.sheet.cell(row=1, column=col)
                cell.font = openpyxl.styles.Font(bold=True)
                cell.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
            
            # 自动调整列宽
            self.sheet.column_dimensions[get_column_letter(col_voucher)].width = 20
            self.sheet.column_dimensions[get_column_letter(col_order)].width = 25
            
            self.update_progress(100, 100, "处理完成！")
            
            # 显示处理结果
            self.update_status("\n🎉 数据处理完成！\n")
            self.update_status("="*50 + "\n")
            self.update_status(f"📊 处理统计:\n")
            self.update_status(f"   • 原数据行数: {total_original_rows}\n")
            self.update_status(f"   • 生成行数: {result_count}\n")
            self.update_status(f"   • 处理效率: {result_count/total_original_rows if total_original_rows > 0 else 0:.1f}倍\n")
            self.update_status("="*50 + "\n\n")
            
            # 启用保存按钮
            self.save_button.config(state=tk.NORMAL)
            
        except Exception as e:
            self.update_status(f"❌ 处理错误: {str(e)}\n")
            self.update_progress(0, 100, "处理失败")
            messagebox.showerror("错误", f"处理数据时发生错误: {str(e)}")
    
    def _handle_merged_cells(self):
        """处理合并单元格"""
        # 获取所有合并单元格范围
        merged_ranges = list(self.sheet.merged_cells.ranges)
        
        if not merged_ranges:
            return
            
        self.update_status(f"🔄 发现 {len(merged_ranges)} 个合并单元格区域，正在取消合并...\n")
        
        # 取消所有合并单元格
        for merged_range in merged_ranges:
            self.sheet.unmerge_cells(str(merged_range))
        
        self.update_status("✅ 所有合并单元格已取消合并\n")
    
    def _get_cell_value(self, row, column):
        """安全获取单元格值"""
        try:
            return self.sheet.cell(row=row, column=column).value
        except:
            return None
    
    def save_file(self):
        """保存结果"""
        if not self.workbook:
            self.update_status("❌ 错误: 没有可保存的数据\n")
            return
            
        # 生成默认文件名
        default_name = "处理结果_" + os.path.basename(self.file_path)
        
        save_path = filedialog.asksaveasfilename(
            title="保存处理结果",
            defaultextension=".xlsx",
            initialfile=default_name,
            filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")]
        )
        
        if save_path:
            try:
                self.update_status("💾 正在保存文件...\n")
                self.update_progress(0, 100, "正在保存文件...")
                
                # 模拟保存进度
                for i in range(1, 101, 10):
                    self.update_progress(i, 100, f"保存中... {i}%")
                    time.sleep(0.05)  # 短暂延迟让用户看到进度
                
                self.workbook.save(save_path)
                self.update_progress(100, 100, "保存完成")
                self.update_status(f"✅ 文件已保存: {save_path}\n\n")
                
                # 询问是否打开文件
                if messagebox.askyesno("保存成功", "文件保存成功！是否立即打开文件？"):
                    os.startfile(save_path)  # Windows
                    # 对于Mac: os.system(f'open "{save_path}"')
                    # 对于Linux: os.system(f'xdg-open "{save_path}"')
                
            except Exception as e:
                self.update_status(f"❌ 保存错误: {str(e)}\n")
                self.update_progress(0, 100, "保存失败")
                messagebox.showerror("错误", f"保存文件时发生错误: {str(e)}")

def main():
    root = tk.Tk()
    app = ExcelProcessor(root)
    root.mainloop()

if __name__ == "__main__":
    main()